<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      db/MySQL系列/08-MySQL系列之-主从复制基础 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>db/MySQL系列/08-MySQL系列之-主从复制基础</h2>



  <p class="post-date">2019-12-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="0-企业高可用性标准"><a href="#0-企业高可用性标准" class="headerlink" title="0.企业高可用性标准 ***"></a>0.企业高可用性标准 ***</h1><h2 id="0-1-全年无故障率-非计划内故障停机"><a href="#0-1-全年无故障率-非计划内故障停机" class="headerlink" title="0.1 全年无故障率(非计划内故障停机)"></a>0.1 全年无故障率(非计划内故障停机)</h2><figure class="highlight rust"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">99.9</span>%                 ----&gt; <span class="number">0.001</span>*<span class="number">365</span>*<span class="number">24</span>*<span class="number">60</span>=<span class="number">525.6</span>  min</span><br><span class="line"><span class="number">99.99</span>%                ----&gt; <span class="number">0.0001</span>*<span class="number">365</span>*<span class="number">24</span>*<span class="number">60</span>=<span class="number">52.56</span> min</span><br><span class="line"><span class="number">99.999</span>%               ----&gt; <span class="number">0.0001</span>*<span class="number">365</span>*<span class="number">24</span>*<span class="number">60</span>=<span class="number">5.256</span> min</span><br></pre></td></tr></table></figure>

<h2 id="0-2-高可用架构方案"><a href="#0-2-高可用架构方案" class="headerlink" title="0.2 高可用架构方案"></a>0.2 高可用架构方案</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">负载均衡:有一定的高可用性 </span><br><span class="line">LVS  Nginx</span><br><span class="line">主备系统:有高可用性,但是需要切换,是单活的架构</span><br><span class="line">KA ,   MHA, MMM</span><br><span class="line">真正高可用(多活系统): </span><br><span class="line">NDB Cluster  Oracle RAC  Sysbase cluster   , InnoDB Cluster（MGR）,PXC , MGC</span><br></pre></td></tr></table></figure>

<h1 id="1-主从复制简介"><a href="#1-主从复制简介" class="headerlink" title="1. 主从复制简介     **"></a>1. 主从复制简介     **</h1><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">1<span class="selector-class">.1</span>. 基于二进制日志复制的</span><br><span class="line">1<span class="selector-class">.2</span>. 主库的修改操作会记录二进制日志</span><br><span class="line">1<span class="selector-class">.3</span>. 从库会请求新的二进制日志并回放,最终达到主从数据同步</span><br><span class="line">1<span class="selector-class">.4</span>. 主从复制核心功能:</span><br><span class="line">辅助备份,处理物理损坏                   </span><br><span class="line">扩展新型的架构:高可用,高性能,分布式架构等</span><br></pre></td></tr></table></figure>

<h1 id="2-主从复制前提-搭建主从的过程"><a href="#2-主从复制前提-搭建主从的过程" class="headerlink" title="2. 主从复制前提(搭建主从的过程)     ***"></a>2. 主从复制前提(搭建主从的过程)     ***</h1><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">## 2<span class="selector-class">.1</span> 两台以上<span class="selector-tag">mysql</span>实例 ,<span class="selector-tag">server_id</span>,<span class="selector-tag">server_uuid</span>不同</span><br><span class="line">## 2<span class="selector-class">.2</span> 主库开启二进制日志</span><br><span class="line">## 2<span class="selector-class">.3</span> 专用的复制用户</span><br><span class="line">## 2<span class="selector-class">.4</span> 保证主从开启之前的某个时间点,从库数据是和主库一致(补课)</span><br><span class="line">## 2<span class="selector-class">.5</span> 告知从库,复制<span class="selector-tag">user</span>,<span class="selector-tag">passwd</span>,<span class="selector-tag">IP</span> <span class="selector-tag">port</span>,以及复制起点(<span class="selector-tag">change</span> <span class="selector-tag">master</span> <span class="selector-tag">to</span>)</span><br><span class="line">## 2<span class="selector-class">.6</span> 线程(三个)<span class="selector-pseudo">:Dump</span> <span class="selector-tag">thread</span>  <span class="selector-tag">IO</span> <span class="selector-tag">thread</span>  <span class="selector-tag">SQL</span> <span class="selector-tag">thread</span> 开启(<span class="selector-tag">start</span> <span class="selector-tag">slave</span>)</span><br></pre></td></tr></table></figure>

<h1 id="3-主从复制搭建-Classic-replication"><a href="#3-主从复制搭建-Classic-replication" class="headerlink" title="3. 主从复制搭建(Classic replication)   ***"></a>3. 主从复制搭建(Classic replication)   ***</h1><h2 id="3-1-清理主库数据"><a href="#3-1-清理主库数据" class="headerlink" title="3.1 清理主库数据"></a>3.1 清理主库数据</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">rm -rf /<span class="keyword">data</span>/<span class="number">3307</span>/<span class="keyword">data</span><span class="comment">/*</span></span><br></pre></td></tr></table></figure>

<h2 id="3-2-重新初始化3307"><a href="#3-2-重新初始化3307" class="headerlink" title="3.2 重新初始化3307"></a>3.2 重新初始化3307</h2><figure class="highlight jsx"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqld --initialize-insecure --user=mysql --basedir=<span class="regexp">/app/my</span>sql --datadir=<span class="regexp">/data/</span><span class="number">3307</span>/data</span><br></pre></td></tr></table></figure>

<h2 id="3-3-修改my-cnf-开启二进制日志功能"><a href="#3-3-修改my-cnf-开启二进制日志功能" class="headerlink" title="3.3 修改my.cnf ,开启二进制日志功能"></a>3.3 修改my.cnf ,开启二进制日志功能</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">[<span class="symbol">root@</span>db01 <span class="number">3307</span>]# vim /<span class="keyword">data</span>/<span class="number">3307</span>/my.cnf </span><br><span class="line">log_bin=/<span class="keyword">data</span>/<span class="number">3307</span>/<span class="keyword">data</span>/mysql-bin</span><br></pre></td></tr></table></figure>

<h2 id="3-4-启动所有节点"><a href="#3-4-启动所有节点" class="headerlink" title="3.4 启动所有节点"></a>3.4 启动所有节点</h2><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># systemctl start mysqld3307</span></span><br><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># systemctl start mysqld3308</span></span><br><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># systemctl start mysqld3309</span></span><br><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># ps -ef |grep mysqld</span></span><br><span class="line">mysql      <span class="number">3684</span>      <span class="number">1</span>  <span class="number">4</span> 09<span class="symbol">:</span><span class="number">59</span> ?        <span class="number">00</span><span class="symbol">:</span><span class="number">00</span><span class="symbol">:</span><span class="number">00</span> /app/mysql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3307</span>/my.cnf</span><br><span class="line">mysql      <span class="number">3719</span>      <span class="number">1</span>  <span class="number">7</span> 09<span class="symbol">:</span><span class="number">59</span> ?        <span class="number">00</span><span class="symbol">:</span><span class="number">00</span><span class="symbol">:</span><span class="number">00</span> /app/mysql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3308</span>/my.cnf</span><br><span class="line">mysql      <span class="number">3754</span>      <span class="number">1</span>  <span class="number">8</span> 09<span class="symbol">:</span><span class="number">59</span> ?        <span class="number">00</span><span class="symbol">:</span><span class="number">00</span><span class="symbol">:</span><span class="number">00</span> /app/mysql/bin/mysqld --defaults-file=<span class="regexp">/data/</span><span class="number">3309</span>/my.cnf</span><br><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># mysql -S /data/3307/mysql.sock -e "select @<span class="doctag">@server</span>_id"</span></span><br><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># mysql -S /data/3308/mysql.sock -e "select @<span class="doctag">@server</span>_id"</span></span><br><span class="line">[root@db01 <span class="number">3307</span>]<span class="comment"># mysql -S /data/3309/mysql.sock -e "select @<span class="doctag">@server</span>_id"</span></span><br></pre></td></tr></table></figure>

<h2 id="3-5-主库中创建复制用户"><a href="#3-5-主库中创建复制用户" class="headerlink" title="3.5 主库中创建复制用户"></a>3.5 主库中创建复制用户</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">[<span class="meta">root@db01 3307</span>]<span class="meta"># mysql -S /data/3307/mysql.sock </span></span><br><span class="line">db01 [(none)]&gt;grant replication slave <span class="keyword">on</span> *.* to repl@<span class="string">'10.0.0.%'</span> identified <span class="keyword">by</span> <span class="string">'123'</span>;</span><br><span class="line">db01 [(none)]&gt;<span class="keyword">select</span> user,host <span class="keyword">from</span> mysql.user;</span><br></pre></td></tr></table></figure>

<h2 id="3-6-备份主库并恢复到从库"><a href="#3-6-备份主库并恢复到从库" class="headerlink" title="3.6 备份主库并恢复到从库"></a>3.6 备份主库并恢复到从库</h2><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">[<span class="symbol">root@</span>db01 <span class="number">3307</span>]# mysqldump -S /<span class="keyword">data</span>/<span class="number">3307</span>/mysql.sock -A --master-<span class="keyword">data</span>=<span class="number">2</span> --single-transaction  -R --triggers &gt;/backup/full.sql</span><br><span class="line">-- CHANGE MASTER TO MASTER_LOG_FILE=<span class="string">'mysql-bin.000001'</span>, MASTER_LOG_POS=<span class="number">653</span>;</span><br><span class="line">[<span class="symbol">root@</span>db01 <span class="number">3307</span>]# mysql -S /<span class="keyword">data</span>/<span class="number">3308</span>/mysql.sock</span><br><span class="line">db01 [(none)]&gt;source /backup/full.sql</span><br></pre></td></tr></table></figure>

<h2 id="3-7-告知从库关键复制信息"><a href="#3-7-告知从库关键复制信息" class="headerlink" title="3.7 告知从库关键复制信息"></a>3.7 告知从库关键复制信息</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">ip port user  password  binlog position </span><br><span class="line">[root@db01 3307]<span class="comment"># mysql -S /data/3308/mysql.sock</span></span><br><span class="line">db01 [mysql]&gt;<span class="built_in">help</span> change master to</span><br><span class="line"></span><br><span class="line">CHANGE MASTER TO</span><br><span class="line">  MASTER_HOST=<span class="string">'10.0.0.51'</span>,</span><br><span class="line">  MASTER_USER=<span class="string">'repl'</span>,</span><br><span class="line">  MASTER_PASSWORD=<span class="string">'123'</span>,</span><br><span class="line">  MASTER_PORT=3307,</span><br><span class="line">  MASTER_LOG_FILE=<span class="string">'mysql-bin.000001'</span>,</span><br><span class="line">  MASTER_LOG_POS=653,</span><br><span class="line">  MASTER_CONNECT_RETRY=10;</span><br></pre></td></tr></table></figure>

<h2 id="3-8-开启主从专用线程"><a href="#3-8-开启主从专用线程" class="headerlink" title="3.8 开启主从专用线程"></a>3.8 开启主从专用线程</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">start slave ;</span><br></pre></td></tr></table></figure>

<h2 id="3-9-检查复制状态"><a href="#3-9-检查复制状态" class="headerlink" title="3.9 检查复制状态"></a>3.9 检查复制状态</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">db01</span> <span class="selector-attr">[mysql]</span>&gt;<span class="selector-tag">show</span> <span class="selector-tag">slave</span>  <span class="selector-tag">status</span> \<span class="selector-tag">G</span></span><br><span class="line"><span class="selector-tag">Slave_IO_Running</span>: <span class="selector-tag">Yes</span></span><br><span class="line"><span class="selector-tag">Slave_SQL_Running</span>: <span class="selector-tag">Yes</span></span><br></pre></td></tr></table></figure>

<h1 id="4-主从复制的原理-Classic-Replication"><a href="#4-主从复制的原理-Classic-Replication" class="headerlink" title="4. 主从复制的原理 (Classic Replication)*****"></a>4. 主从复制的原理 (Classic Replication)*****</h1><h2 id="4-1-主从中设置到的文件和线程"><a href="#4-1-主从中设置到的文件和线程" class="headerlink" title="4.1 主从中设置到的文件和线程"></a>4.1 主从中设置到的文件和线程</h2><h3 id="4-1-1-线程"><a href="#4-1-1-线程" class="headerlink" title="4.1.1 线程"></a>4.1.1 线程</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">主:</span><br><span class="line">DUMP THREAD</span><br><span class="line">从:</span><br><span class="line">IO  THREAD</span><br><span class="line">SQL THREAD</span><br></pre></td></tr></table></figure>

<h3 id="4-1-2-文件"><a href="#4-1-2-文件" class="headerlink" title="4.1.2 文件"></a>4.1.2 文件</h3><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">主:</span><br><span class="line">mysql-bin<span class="number">.000001</span></span><br><span class="line">从: </span><br><span class="line">db01-relay<span class="number">.000001</span>     ===&gt;中继日志</span><br><span class="line">master.info                 ===》主库信息记录日志</span><br><span class="line">relay-log.info              ===&gt; 记录中继应用情况信息</span><br></pre></td></tr></table></figure>

<h1 id="4-2-主从复制原理"><a href="#4-2-主从复制原理" class="headerlink" title="4.2 主从复制原理"></a>4.2 主从复制原理</h1><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-72dd1f45d206d507.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/693/format/webp" alt="img"></p>
<p>image.png</p>
<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-a4273ecc8aa1c370.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/724/format/webp" alt="img"></p>
<p>image.png</p>
<p>主从复制原理描述：</p>
<figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span>change master to 时，ip pot user password binlog position写入到master.info进行记录</span><br><span class="line"><span class="number">2.</span> start slave 时，从库会启动IO线程和SQL线程</span><br><span class="line"><span class="number">3.</span>IO_T，读取master.info信息，获取主库信息连接主库</span><br><span class="line"><span class="number">4.</span> 主库会生成一个准备binlog DUMP线程，来响应从库</span><br><span class="line"><span class="number">5.</span> IO_T根据master.info记录的binlog文件名和position号，请求主库DUMP最新日志</span><br><span class="line"><span class="number">6.</span> DUMP线程检查主库的binlog日志，如果有新的，TP(传送)给从从库的IO_T</span><br><span class="line"><span class="number">7.</span> IO_T将收到的日志存储到了TCP/IP 缓存，立即返回ACK给主库 ，主库工作完成</span><br><span class="line"><span class="number">8.</span>IO_T将缓存中的数据，存储到relay-<span class="built_in">log</span>日志文件,更新master.info文件binlog 文件名和postion，IO_T工作完成</span><br><span class="line"><span class="number">9.</span>SQL_T读取relay-<span class="built_in">log</span>.info文件，获取到上次执行到的relay-<span class="built_in">log</span>的位置，作为起点，回放relay-<span class="built_in">log</span></span><br><span class="line"><span class="number">10.</span>SQL_T回放完成之后，会更新relay-<span class="built_in">log</span>.info文件。</span><br><span class="line"><span class="number">11.</span> relay-<span class="built_in">log</span>会有自动清理的功能。</span><br><span class="line">细节：</span><br><span class="line"><span class="number">1.</span>主库一旦有新的日志生成，会发送“信号”给binlog dump ，IO线程再请求</span><br></pre></td></tr></table></figure>

<h1 id="5-主从故障监控-分析-处理"><a href="#5-主从故障监控-分析-处理" class="headerlink" title="5. 主从故障监控\分析\处理 *****"></a>5. 主从故障监控\分析\处理 *****</h1><h2 id="5-1-线程相关监控"><a href="#5-1-线程相关监控" class="headerlink" title="5.1 线程相关监控"></a>5.1 线程相关监控</h2><h2 id="主库"><a href="#主库" class="headerlink" title="主库:"></a>主库:</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">show</span> <span class="selector-tag">full</span> <span class="selector-tag">processlist</span>;</span><br><span class="line">每个从库都会有一行<span class="selector-tag">dump</span>相关的信息</span><br><span class="line"><span class="selector-tag">HOSTS</span>: </span><br><span class="line"><span class="selector-tag">db01</span><span class="selector-pseudo">:47176</span></span><br><span class="line"><span class="selector-tag">State</span>:</span><br><span class="line"><span class="selector-tag">Master</span> <span class="selector-tag">has</span> <span class="selector-tag">sent</span> <span class="selector-tag">all</span> <span class="selector-tag">binlog</span> <span class="selector-tag">to</span> <span class="selector-tag">slave</span>; <span class="selector-tag">waiting</span> <span class="selector-tag">for</span> <span class="selector-tag">more</span> <span class="selector-tag">updates</span></span><br><span class="line">如果现实非以上信息,说明主从之间的关系出现了问题</span><br></pre></td></tr></table></figure>

<h2 id="从库"><a href="#从库" class="headerlink" title="从库:"></a>从库:</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">db01</span> <span class="selector-attr">[(none)]</span>&gt;<span class="selector-tag">show</span> <span class="selector-tag">slave</span> <span class="selector-tag">status</span> \<span class="selector-tag">G</span></span><br><span class="line">*************************** 1. <span class="selector-tag">row</span> ***************************</span><br></pre></td></tr></table></figure>

<h2 id="主库相关信息监控"><a href="#主库相关信息监控" class="headerlink" title="主库相关信息监控"></a>主库相关信息监控</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">Master_Host</span>: 10<span class="selector-class">.0</span><span class="selector-class">.0</span><span class="selector-class">.51</span></span><br><span class="line"><span class="selector-tag">Master_User</span>: <span class="selector-tag">repl</span></span><br><span class="line"><span class="selector-tag">Master_Port</span>: 3307</span><br><span class="line"><span class="selector-tag">Master_Log_File</span>: <span class="selector-tag">mysql-bin</span><span class="selector-class">.000005</span></span><br><span class="line"><span class="selector-tag">Read_Master_Log_Pos</span>: 444</span><br></pre></td></tr></table></figure>

<h2 id="从库中继日志的应用状态"><a href="#从库中继日志的应用状态" class="headerlink" title="从库中继日志的应用状态"></a>从库中继日志的应用状态</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">Relay_Log_File</span>: <span class="selector-tag">db01-relay-bin</span><span class="selector-class">.000002</span></span><br><span class="line"><span class="selector-tag">Relay_Log_Pos</span>: 485</span><br></pre></td></tr></table></figure>

<h2 id="从库复制线程有关的状态"><a href="#从库复制线程有关的状态" class="headerlink" title="从库复制线程有关的状态"></a>从库复制线程有关的状态</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">Slave_IO_Running: Yes</span><br><span class="line">Slave_SQL_Running: Yes</span><br><span class="line">Last_IO_Errno: 0</span><br><span class="line">Last_IO_Error: </span><br><span class="line">Last_SQL_Errno: 0</span><br><span class="line">Last_SQL_Error:</span><br></pre></td></tr></table></figure>

<h2 id="过滤复制有关的状态"><a href="#过滤复制有关的状态" class="headerlink" title="过滤复制有关的状态"></a>过滤复制有关的状态</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">Replicate_Do_DB: </span><br><span class="line">Replicate_Ignore_DB: </span><br><span class="line">Replicate_Do_Table: </span><br><span class="line">Replicate_Ignore_Table: </span><br><span class="line">Replicate_Wild_Do_Table: </span><br><span class="line">Replicate_Wild_Ignore_Table:</span><br></pre></td></tr></table></figure>

<h2 id="主从延时相关状态-非人为"><a href="#主从延时相关状态-非人为" class="headerlink" title="主从延时相关状态(非人为)"></a>主从延时相关状态(非人为)</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">Seconds_Behind_Master: 0</span><br></pre></td></tr></table></figure>

<h2 id="延时从库有关的状态-人为"><a href="#延时从库有关的状态-人为" class="headerlink" title="延时从库有关的状态(人为)"></a>延时从库有关的状态(人为)</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SQL_Delay: <span class="number">0</span></span><br><span class="line">SQL_Remaining_Delay: <span class="literal">NULL</span></span><br></pre></td></tr></table></figure>

<h2 id="GTID-复制有关的状态"><a href="#GTID-复制有关的状态" class="headerlink" title="GTID 复制有关的状态"></a>GTID 复制有关的状态</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">Retrieved_Gtid_Set: </span><br><span class="line">Executed_Gtid_Set: </span><br><span class="line">Auto_Position: 0</span><br></pre></td></tr></table></figure>

<h2 id="5-2-主从复制故障分析"><a href="#5-2-主从复制故障分析" class="headerlink" title="5.2  主从复制故障分析"></a>5.2  主从复制故障分析</h2><h3 id="5-2-1-IO"><a href="#5-2-1-IO" class="headerlink" title="5.2.1 IO"></a>5.2.1 IO</h3><h4 id="5-2-1-1-连接主库"><a href="#5-2-1-1-连接主库" class="headerlink" title="5.2.1.1 连接主库"></a>5.2.1.1 连接主库</h4><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">(<span class="number">1</span>) 用户 密码  IP  port</span><br><span class="line">Last_IO_Error: error reconnecting to master <span class="string">'repl@10.0.0.51:3307'</span> - retry-time: <span class="number">10</span>  retries: <span class="number">7</span></span><br><span class="line">[<span class="meta">root@db01 ~</span>]<span class="meta"># mysql -urepl  -p123333  -h 10.0.0.51 -P 3307</span></span><br><span class="line">ERROR <span class="number">1045</span> (<span class="number">28000</span>): Access denied <span class="keyword">for</span> user <span class="string">'repl'</span>@<span class="string">'db01'</span> (<span class="keyword">using</span> password: YES)</span><br><span class="line"></span><br><span class="line">原因:</span><br><span class="line">密码错误 </span><br><span class="line">用户错误 </span><br><span class="line">skip_name_resolve</span><br><span class="line">地址错误</span><br><span class="line">端口</span><br></pre></td></tr></table></figure>

<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-2d45278fb16e4d69.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/932/format/webp" alt="img"></p>
<p>image.png</p>
<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-0ed17c75c871d787.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/975/format/webp" alt="img"></p>
<p>image.png</p>
<h2 id="处理方法"><a href="#处理方法" class="headerlink" title="处理方法"></a>处理方法</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">stop  slave  </span><br><span class="line">reset slave all </span><br><span class="line">change master to </span><br><span class="line">start slave</span><br></pre></td></tr></table></figure>

<h2 id="主库连接数上线-或者是主库太繁忙"><a href="#主库连接数上线-或者是主库太繁忙" class="headerlink" title="主库连接数上线,或者是主库太繁忙"></a>主库连接数上线,或者是主库太繁忙</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> slave  staus \G </span><br><span class="line">Last_IO_Errno: <span class="number">1040</span></span><br><span class="line">Last_IO_Error: error reconnecting to master <span class="string">'repl@10.0.0.51:3307'</span> - retry-time: <span class="number">10</span>  retries: <span class="number">7</span></span><br><span class="line">处理思路:</span><br><span class="line">拿复制用户,手工连接一下</span><br><span class="line"></span><br><span class="line">[root<span class="meta">@db</span>01 ~]# mysql -urepl -p123 -h <span class="number">10.0</span><span class="number">.0</span><span class="number">.51</span> -P <span class="number">3307</span> </span><br><span class="line">mysql: [Warning] Using a password <span class="keyword">on</span> the command line <span class="class"><span class="keyword">interface</span> <span class="title">can</span> <span class="title">be</span> <span class="title">insecure</span>.</span></span><br><span class="line"><span class="class"><span class="title">ERROR</span> 1040 (<span class="title">HY000</span>): <span class="title">Too</span> <span class="title">many</span> <span class="title">connections</span></span></span><br><span class="line"><span class="class">处理方法:</span></span><br><span class="line"><span class="class"><span class="title">db01</span> [(<span class="title">none</span>)]&gt;<span class="title">set</span> <span class="title">global</span> <span class="title">max_connections</span>=300;</span></span><br><span class="line"><span class="class"></span></span><br><span class="line"><span class="class">(3) 防火墙,网络不通</span></span><br></pre></td></tr></table></figure>

<h4 id="5-2-1-2-请求二进制日志"><a href="#5-2-1-2-请求二进制日志" class="headerlink" title="5.2.1.2 请求二进制日志"></a>5.2.1.2 请求二进制日志</h4><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">主库缺失日志</span><br><span class="line">从库方面,二进制日志位置点不对</span><br><span class="line">Last_IO_Error: Got fatal error <span class="number">1236</span> <span class="keyword">from</span> master <span class="keyword">when</span> reading data <span class="keyword">from</span> binary log: <span class="string">'could not find next log; the first event '</span>mysql-bin<span class="number">.000001'</span> at <span class="number">154</span>, the last <span class="keyword">event</span> read <span class="keyword">from</span> <span class="string">'/data/3307/data/mysql-bin.000002'</span> at <span class="number">154</span>, the last <span class="keyword">byte</span> read <span class="keyword">from</span> <span class="string">'/data/3307/data/mysql-bin.000002'</span> at <span class="number">154.'</span></span><br></pre></td></tr></table></figure>

<p><img src="https:////upload-images.jianshu.io/upload_images/16956686-78c7eaaacd175fc0.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1200/format/webp" alt="img"></p>
<p>image.png</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">注意: 在主从复制环境中,严令禁止主库中reset master; 可以选择expire 进行定期清理主库二进制日志</span><br><span class="line">解决方案:</span><br><span class="line">重新构建主从</span><br></pre></td></tr></table></figure>

<h3 id="5-2-2-SQL-线程故障"><a href="#5-2-2-SQL-线程故障" class="headerlink" title="5.2.2 SQL 线程故障"></a>5.2.2 SQL 线程故障</h3><h2 id="SQL线程功能："><a href="#SQL线程功能：" class="headerlink" title="SQL线程功能："></a>SQL线程功能：</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">(<span class="number">1</span>)读写relay-<span class="built_in">log</span>.info </span><br><span class="line">(<span class="number">2</span>)relay-<span class="built_in">log</span>损坏,断节,找不到</span><br><span class="line">(<span class="number">3</span>)接收到的SQL无法执行</span><br></pre></td></tr></table></figure>

<h2 id="导致SQL线程故障原因分析："><a href="#导致SQL线程故障原因分析：" class="headerlink" title="导致SQL线程故障原因分析："></a>导致SQL线程故障原因分析：</h2><figure class="highlight rust"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1</span>. 版本差异，参数设定不同，比如：数据类型的差异，SQL_MODE影响</span><br><span class="line"><span class="number">2</span>.要创建的数据库对象,已经存在</span><br><span class="line"><span class="number">3</span>.要删除或修改的对象不存在  </span><br><span class="line"><span class="number">4</span>.DML语句不符合表定义及约束时.  </span><br><span class="line">归根揭底的原因都是由于从库发生了写入操作.</span><br><span class="line">Last_SQL_Error: Error <span class="symbol">'Can</span><span class="symbol">'t</span> create database <span class="symbol">'db</span>'; database exists' on query. <span class="built_in">Default</span> database: <span class="symbol">'db</span>'. Query: <span class="symbol">'create</span> database db'</span><br></pre></td></tr></table></figure>

<h2 id="处理方法-以从库为核心的处理方案-："><a href="#处理方法-以从库为核心的处理方案-：" class="headerlink" title="处理方法(以从库为核心的处理方案)："></a>处理方法(以从库为核心的处理方案)：</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">方法一：</span><br><span class="line">stop slave; </span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> sql_slave_skip_counter = <span class="number">1</span>;</span><br><span class="line"><span class="meta">#将同步指针向下移动一个，如果多次不同步，可以重复操作。</span></span><br><span class="line">start slave;</span><br><span class="line">方法二：</span><br><span class="line">/etc/my.cnf</span><br><span class="line">slave-skip-errors = <span class="number">1032</span>,<span class="number">1062</span>,<span class="number">1007</span></span><br><span class="line">常见错误代码:</span><br><span class="line"><span class="number">1007</span>:对象已存在</span><br><span class="line"><span class="number">1032</span>:无法执行DML</span><br><span class="line"><span class="number">1062</span>:主键冲突,或约束冲突</span><br><span class="line"></span><br><span class="line">但是，以上操作有时是有风险的，最安全的做法就是重新构建主从。把握一个原则,一切以主库为主.</span><br></pre></td></tr></table></figure>

<h2 id="一劳永逸的方法"><a href="#一劳永逸的方法" class="headerlink" title="一劳永逸的方法:"></a>一劳永逸的方法:</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">(<span class="number">1</span>) 可以设置从库只读.</span><br><span class="line">db01 [(none)]&gt;<span class="keyword">show</span> variables like <span class="string">'%read_only%'</span>;</span><br><span class="line">注意：</span><br><span class="line">只会影响到普通用户，对管理员用户无效。</span><br><span class="line">(<span class="number">2</span>)加中间件</span><br><span class="line">读写分离。</span><br></pre></td></tr></table></figure>

<h1 id="6-主从延时监控及原因"><a href="#6-主从延时监控及原因" class="headerlink" title="6. 主从延时监控及原因     *****"></a>6. 主从延时监控及原因     *****</h1><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">主库做了修改操作,从库比较长时间才能追上.</span><br></pre></td></tr></table></figure>

<h2 id="6-1-外在因素"><a href="#6-1-外在因素" class="headerlink" title="6.1  外在因素"></a>6.1  外在因素</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">网络 </span><br><span class="line">主从硬件差异较大</span><br><span class="line">版本差异</span><br><span class="line">参数因素</span><br></pre></td></tr></table></figure>

<h2 id="6-2-主库"><a href="#6-2-主库" class="headerlink" title="6.2 主库"></a>6.2 主库</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">(1) 二进制日志写入不及时</span><br><span class="line"><span class="selector-attr">[rep]</span>&gt;<span class="selector-tag">select</span> @<span class="keyword">@sync_binlog</span>;</span><br><span class="line">(2) <span class="selector-tag">CR</span>的主从复制中,<span class="selector-tag">binlog_dump</span>线程,事件为单元,串行传送二进制日志(5<span class="selector-class">.6</span> 5<span class="selector-class">.5</span>)</span><br><span class="line"></span><br><span class="line">1. 主库并发事务量大,主库可以并行,传送时是串行</span><br><span class="line">2. 主库发生了大事务,由于是串行传送,会产生阻塞后续的事务.</span><br><span class="line"></span><br><span class="line">解决方案:</span><br><span class="line">1. 5<span class="selector-class">.6</span> 开始,开启<span class="selector-tag">GTID</span>,实现了<span class="selector-tag">GC</span>(<span class="selector-tag">group</span> <span class="selector-tag">commit</span>)机制,可以并行传输日志给从库<span class="selector-tag">IO</span></span><br><span class="line">2. 5<span class="selector-class">.7</span> 开始,不开启<span class="selector-tag">GTID</span>,会自动维护匿名的<span class="selector-tag">GTID</span>,也能实现<span class="selector-tag">GC</span>,我们建议还是认为开启<span class="selector-tag">GTID</span></span><br><span class="line">3. 大事务拆成多个小事务,可以有效的减少主从延时.</span><br></pre></td></tr></table></figure>

<h2 id="6-3-从库"><a href="#6-3-从库" class="headerlink" title="6.3  从库"></a>6.3  从库</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">SQL</span>线程导致的主从延时</span><br><span class="line">在<span class="selector-tag">CR</span>复制情况下: 从库默认情况下只有一个<span class="selector-tag">SQL</span>,只能串行回放事务<span class="selector-tag">SQL</span></span><br><span class="line">1. 主库如果并发事务量较大,从库只能串行回放</span><br><span class="line">2. 主库发生了大事务,会阻塞后续的所有的事务的运行</span><br><span class="line"></span><br><span class="line">解决方案:</span><br><span class="line">1. 5<span class="selector-class">.6</span> 版本开启<span class="selector-tag">GTID</span>之后,加入了<span class="selector-tag">SQL</span>多线程的特性,但是只能针对不同库(<span class="selector-tag">database</span>)下的事务进行并发回放.</span><br><span class="line">2. 5<span class="selector-class">.7</span> 版本开始<span class="selector-tag">GTID</span>之后,在<span class="selector-tag">SQL</span>方面,提供了基于逻辑时钟(<span class="selector-tag">logical_clock</span>),<span class="selector-tag">binlog</span>加入了<span class="selector-tag">seq_no</span>机制,</span><br><span class="line">真正实现了基于事务级别的并发回放,这种技术我们把它称之为<span class="selector-tag">MTS</span>(<span class="selector-tag">enhanced</span> <span class="selector-tag">multi-threaded</span> <span class="selector-tag">slave</span>).</span><br><span class="line">3. 大事务拆成多个小事务,可以有效的减少主从延时.</span><br><span class="line"><span class="selector-attr">[https://dev.mysql.com/worklog/task/?id=6314]</span></span><br></pre></td></tr></table></figure>

<h1 id="7-小结"><a href="#7-小结" class="headerlink" title="7. 小结"></a>7. 小结</h1><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span> 主从复制原理</span><br><span class="line"><span class="number">2.</span> 主从复制故障</span><br><span class="line"><span class="number">3.</span> 主从延时：<span class="keyword">group</span> commit    MTS</span><br></pre></td></tr></table></figure>

</section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/db/MySQL%E7%B3%BB%E5%88%97/02-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-MySQL%E4%BD%93%E7%B3%BB%E7%BB%93%E6%9E%84%E4%B8%8E%E7%AE%A1%E7%90%86/">
        <span class="nav-arrow">← </span>
        
          db/MySQL系列/02-MySQL系列之-MySQL体系结构与管理
        
      </a>
    
    
      <a class="nav-right" href="/db/Redis%E7%B3%BB%E5%88%97/00-Redis%E7%B3%BB%E5%88%97%E4%B9%8B-Redis%E4%BB%8B%E7%BB%8D%E5%AE%89%E8%A3%85%E9%85%8D%E7%BD%AE/">
        
          db/Redis系列/00-Redis系列之-Redis介绍安装配置
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#0-企业高可用性标准"><span class="toc-nav-text">0.企业高可用性标准 ***</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#0-1-全年无故障率-非计划内故障停机"><span class="toc-nav-text">0.1 全年无故障率(非计划内故障停机)</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#0-2-高可用架构方案"><span class="toc-nav-text">0.2 高可用架构方案</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#1-主从复制简介"><span class="toc-nav-text">1. 主从复制简介     **</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#2-主从复制前提-搭建主从的过程"><span class="toc-nav-text">2. 主从复制前提(搭建主从的过程)     ***</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#3-主从复制搭建-Classic-replication"><span class="toc-nav-text">3. 主从复制搭建(Classic replication)   ***</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-1-清理主库数据"><span class="toc-nav-text">3.1 清理主库数据</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-2-重新初始化3307"><span class="toc-nav-text">3.2 重新初始化3307</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-3-修改my-cnf-开启二进制日志功能"><span class="toc-nav-text">3.3 修改my.cnf ,开启二进制日志功能</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-4-启动所有节点"><span class="toc-nav-text">3.4 启动所有节点</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-5-主库中创建复制用户"><span class="toc-nav-text">3.5 主库中创建复制用户</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-6-备份主库并恢复到从库"><span class="toc-nav-text">3.6 备份主库并恢复到从库</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-7-告知从库关键复制信息"><span class="toc-nav-text">3.7 告知从库关键复制信息</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-8-开启主从专用线程"><span class="toc-nav-text">3.8 开启主从专用线程</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-9-检查复制状态"><span class="toc-nav-text">3.9 检查复制状态</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#4-主从复制的原理-Classic-Replication"><span class="toc-nav-text">4. 主从复制的原理 (Classic Replication)*****</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#4-1-主从中设置到的文件和线程"><span class="toc-nav-text">4.1 主从中设置到的文件和线程</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-1-1-线程"><span class="toc-nav-text">4.1.1 线程</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-1-2-文件"><span class="toc-nav-text">4.1.2 文件</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#4-2-主从复制原理"><span class="toc-nav-text">4.2 主从复制原理</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#5-主从故障监控-分析-处理"><span class="toc-nav-text">5. 主从故障监控\分析\处理 *****</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#5-1-线程相关监控"><span class="toc-nav-text">5.1 线程相关监控</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#主库"><span class="toc-nav-text">主库:</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#从库"><span class="toc-nav-text">从库:</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#主库相关信息监控"><span class="toc-nav-text">主库相关信息监控</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#从库中继日志的应用状态"><span class="toc-nav-text">从库中继日志的应用状态</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#从库复制线程有关的状态"><span class="toc-nav-text">从库复制线程有关的状态</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#过滤复制有关的状态"><span class="toc-nav-text">过滤复制有关的状态</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#主从延时相关状态-非人为"><span class="toc-nav-text">主从延时相关状态(非人为)</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#延时从库有关的状态-人为"><span class="toc-nav-text">延时从库有关的状态(人为)</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#GTID-复制有关的状态"><span class="toc-nav-text">GTID 复制有关的状态</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#5-2-主从复制故障分析"><span class="toc-nav-text">5.2  主从复制故障分析</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#5-2-1-IO"><span class="toc-nav-text">5.2.1 IO</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#5-2-1-1-连接主库"><span class="toc-nav-text">5.2.1.1 连接主库</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#处理方法"><span class="toc-nav-text">处理方法</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#主库连接数上线-或者是主库太繁忙"><span class="toc-nav-text">主库连接数上线,或者是主库太繁忙</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#5-2-1-2-请求二进制日志"><span class="toc-nav-text">5.2.1.2 请求二进制日志</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#5-2-2-SQL-线程故障"><span class="toc-nav-text">5.2.2 SQL 线程故障</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#SQL线程功能："><span class="toc-nav-text">SQL线程功能：</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#导致SQL线程故障原因分析："><span class="toc-nav-text">导致SQL线程故障原因分析：</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#处理方法-以从库为核心的处理方案-："><span class="toc-nav-text">处理方法(以从库为核心的处理方案)：</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#一劳永逸的方法"><span class="toc-nav-text">一劳永逸的方法:</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#6-主从延时监控及原因"><span class="toc-nav-text">6. 主从延时监控及原因     *****</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-1-外在因素"><span class="toc-nav-text">6.1  外在因素</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-2-主库"><span class="toc-nav-text">6.2 主库</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-3-从库"><span class="toc-nav-text">6.3  从库</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#7-小结"><span class="toc-nav-text">7. 小结</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/db/MySQL系列/08-MySQL系列之-主从复制基础/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "db/MySQL系列/08-MySQL系列之-主从复制基础",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>